package dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import common.GetConnection;
import vo.AtdVO;


public class AtdDAO {
	
/*/////////////////////////////////////Create///////////////////////////////////*/
public void set_std_info(String name, String birth, String school, String telephone, String email) throws SQLException {
	 System.out.println(name);
	Connection conn = null;
	PreparedStatement pstmt = null;
		
	try{
		conn= GetConnection.getConnection();
		pstmt = conn.prepareStatement("insert into stdTBL(name, birth, school, telephone, email) values(?, ?, ?, ?, ?)");
		
		pstmt.setString(1, name);
		pstmt.setString(2, birth);
		pstmt.setString(3, school);
		pstmt.setString(4, telephone);
		pstmt.setString(5, email);
			
		pstmt.executeUpdate();
		
	} finally {
	        if (pstmt != null) try { pstmt.close(); } catch(SQLException ex) {}
            if (conn != null) try { conn.close(); } catch(SQLException ex) {}	
	}
			
}
	

//	public void set_atd_info(int stdNo, Date atdTime, int status, String detail){
		// insert into atdTBL (stdNo, atdTime, status, detail) values(2, "2014-07-01 12:01:34", "1", "몰라")
//	}
	
	
	
	
/*/////////////////////////////////////Read/////////////////////////////////////*/
public List<AtdVO> get_daily_list(String atdTime) throws SQLException{
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	try{
		conn = GetConnection.getConnection();					//DB와의 커넥션을 생성해라.
		pstmt = conn.prepareStatement("select a.stdNo, a.name, a.school, a.telephone, b.status, b.atdTime, b.detail from stdTBL a left outer join atdTBL b  on a.stdNo = b.stdNo where DATE_FORMAT(b.atdTime,'%Y-%m-%d') = ? or b.atdTime is null order by a.stdNo"); 
		pstmt.setString(1, atdTime);
		rs = pstmt.executeQuery();
		if(rs.next()){			//DB에서 블로그의 게시글이 있다면 VO로 값을 넘겨줘라.
			List<AtdVO> dailyList = new ArrayList<AtdVO>();
			do{
				AtdVO atdvo = new AtdVO();
				atdvo.setStdNo(rs.getInt(1));
				atdvo.setName(rs.getString(2));
				atdvo.setSchool(rs.getString(3));
				atdvo.setTelephone(rs.getString(4));
				atdvo.setStatus(rs.getInt(5));
				atdvo.setAtdTime(rs.getTimestamp(6));
				atdvo.setDetail(rs.getString(7));
				
				dailyList.add(atdvo);
			}while(rs.next());
			return dailyList;
		}
		else{
			return Collections.emptyList();
		}
	}
	
	finally{
		if(rs != null) try{rs.close();} catch(SQLException ex){}
		if(pstmt != null) try{pstmt.close();} catch(SQLException ex){}
		if(conn != null) try{conn.close();} catch(SQLException ex){}
	}	
}
	
//	public List<AtdVO> get_One_List(int stdNo, Date atdTime) throws SQLException{
		/* 
		   select a.stdNo, a.name, a.birth, a.school, a.telephone, a.email, DATE_FORMAT(b.atdTime,'%H:%i') as atdTime, b.status, b.detail
		   from stdTBL a, atdTBL b where a.stdNo = b.stdNo and DATE_FORMAT(b.atdTime,'%Y-%m-%d') = '2014-07-17' and a.stdNo = 1
		*/
//	}
	
//	public List<AtdVO> get_Monthly_List(int stdNo, Date atdTime) throws SQLException{
		
//	}

	
	
	
/*/////////////////////////////////////Update///////////////////////////////////*/
//	public void update_stu_info(int stdNo){
		// update stdTBL set name="kim1", birth="910203", school="VISION3", telephone="010-2222-1323", email="kim1@naver.com" where stdNo = 1
//	}
	
//	public void update_Atd_info(int stdNo, Date atdTime, int status, String detail){
		/*
		   update atdTBL set atdTime="2014-07-17 14:37:17", status="0", detail=""
		   where stdNo = 2 and DATE_FORMAT(atdTime, '%Y-%m-%d') = '2014-07-17'
		*/
//	}
	
	
	
	
/*/////////////////////////////////////Delete///////////////////////////////////*/
//	public void delet_stu_info(int stdNo){
		
//	}
	
	
	
}
